"""Rename product tables to include product_ prefix

Revision ID: 20241005_rename_tables
Revises: 20241005_add_indexes
Create Date: 2025-10-05 18:40:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision: str = '20241005_rename_tables'
down_revision: Union[str, Sequence[str], None] = '20241005_add_indexes'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema - Rename tables to include product_ prefix"""
    # ### commands auto generated by Alembic - please adjust! ###

    # Rename attribute tables
    op.rename_table('attribute_definitions', 'product_attribute_definitions')
    op.rename_table('attribute_values', 'product_attribute_values')

    # Rename specification tables
    op.rename_table('specification_groups', 'product_specification_groups')
    op.rename_table('specification_definitions', 'product_specification_definitions')

    # Drop old indexes that reference old table names and recreate with new names
    # Attribute definitions indexes
    try:
        op.drop_index('idx_attr_def_code', table_name='attribute_definitions')
    except:
        pass
    try:
        op.drop_index('idx_attr_def_is_active', table_name='attribute_definitions')
    except:
        pass

    # Create new indexes for renamed tables
    op.create_index('idx_product_attr_def_code', 'product_attribute_definitions', ['code'], unique=False)
    op.create_index('idx_product_attr_def_is_active', 'product_attribute_definitions', ['is_active'], unique=False)

    # Attribute values indexes
    try:
        op.drop_index('idx_attr_value_attribute_id', table_name='attribute_values')
    except:
        pass
    try:
        op.drop_index('idx_attr_value_is_active', table_name='attribute_values')
    except:
        pass

    # Create new indexes for renamed tables
    op.create_index('idx_product_attr_value_attribute_id', 'product_attribute_values', ['attribute_id'], unique=False)
    op.create_index('idx_product_attr_value_is_active', 'product_attribute_values', ['is_active'], unique=False)

    # Specification groups indexes
    try:
        op.drop_index('idx_spec_group_is_active', table_name='specification_groups')
    except:
        pass

    # Create new indexes for renamed tables
    op.create_index('idx_product_spec_group_is_active', 'product_specification_groups', ['is_active'], unique=False)

    # Specification definitions indexes
    try:
        op.drop_index('idx_spec_def_group_id', table_name='specification_definitions')
    except:
        pass
    try:
        op.drop_index('idx_spec_def_code', table_name='specification_definitions')
    except:
        pass
    try:
        op.drop_index('idx_spec_def_is_active', table_name='specification_definitions')
    except:
        pass

    # Create new indexes for renamed tables
    op.create_index('idx_product_spec_def_group_id', 'product_specification_definitions', ['group_id'], unique=False)
    op.create_index('idx_product_spec_def_code', 'product_specification_definitions', ['code'], unique=False)
    op.create_index('idx_product_spec_def_is_active', 'product_specification_definitions', ['is_active'], unique=False)

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema - Revert table names"""
    # ### commands auto generated by Alembic - please adjust! ###

    # Drop new indexes
    op.drop_index('idx_product_spec_def_is_active', table_name='product_specification_definitions')
    op.drop_index('idx_product_spec_def_code', table_name='product_specification_definitions')
    op.drop_index('idx_product_spec_def_group_id', table_name='product_specification_definitions')
    op.drop_index('idx_product_spec_group_is_active', table_name='product_specification_groups')
    op.drop_index('idx_product_attr_value_is_active', table_name='product_attribute_values')
    op.drop_index('idx_product_attr_value_attribute_id', table_name='product_attribute_values')
    op.drop_index('idx_product_attr_def_is_active', table_name='product_attribute_definitions')
    op.drop_index('idx_product_attr_def_code', table_name='product_attribute_definitions')

    # Recreate old indexes
    op.create_index('idx_spec_def_is_active', 'specification_definitions', ['is_active'], unique=False)
    op.create_index('idx_spec_def_code', 'specification_definitions', ['code'], unique=False)
    op.create_index('idx_spec_def_group_id', 'specification_definitions', ['group_id'], unique=False)
    op.create_index('idx_spec_group_is_active', 'specification_groups', ['is_active'], unique=False)
    op.create_index('idx_attr_value_is_active', 'attribute_values', ['is_active'], unique=False)
    op.create_index('idx_attr_value_attribute_id', 'attribute_values', ['attribute_id'], unique=False)
    op.create_index('idx_attr_def_is_active', 'attribute_definitions', ['is_active'], unique=False)
    op.create_index('idx_attr_def_code', 'attribute_definitions', ['code'], unique=False)

    # Rename tables back to original names
    op.rename_table('product_specification_definitions', 'specification_definitions')
    op.rename_table('product_specification_groups', 'specification_groups')
    op.rename_table('product_attribute_values', 'attribute_values')
    op.rename_table('product_attribute_definitions', 'attribute_definitions')

    # ### end Alembic commands ###